﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ParkingLot.Entitys.DbSessionEntitys;
using ParkingLot.Entitys.Enums;
using ParkingLot.Utility.Factory;
using ParkingLot.Utility.LogHelps;

namespace ParkingLot.Utility.DBConnection.SqlSever
{
    /// <summary>
    /// 连接数据库的帮助类
    /// </summary>
    public class SqlSeverDbSession : IDisposable, IDBSession
    {
        #region 字段
        /// <summary>
        /// 连接字符串
        /// </summary>
        private static readonly string m_ConnectionString = SqlDbSessionResource.Instance.ConnectionString;

        private SqlConnection m_Connection;

        /// <summary>
        /// 数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            get { return m_Connection; }
            private set { m_Connection = value; }
        }

        private SqlTransaction m_Transaction;

        /// <summary>
        /// 数据库事务
        /// </summary>
        public SqlTransaction Transaction
        {
            get { return m_Transaction; }
            private set { m_Transaction = value; }
        }

        #endregion

        public SqlSeverDbSession(string connectionString = null)
        {
            try
            {
                if (string.IsNullOrEmpty(connectionString))
                {
                    connectionString = m_ConnectionString;
                }

                m_Connection = new SqlConnection(connectionString);
                if (m_Connection.State == ConnectionState.Closed)
                {
                    m_Connection.Open();
                }
            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }

        }

        #region 命令

        /// <summary>
        /// 2012-2-21新增重载，执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事务</param>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    trans.Rollback();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 2012-2-21新增重载，执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事务</param>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object ExecuteScalar(DbSessionEntity dbSessionEntity, out string error)
        {
            error = "";
            try
            {
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), m_Connection);
                if (m_Transaction != null)
                {
                    cmd.Transaction = m_Transaction;
                }
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    error = "没有返回值";
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (Exception exception)
            {
                error = "数据库会话异常" + exception.ToString();
                return null;
            }
        }



        public int ExecuteCommand(DbSessionEntity dbSessionEntity, out string error)
        {
            error = "";
            int result = -2;
            try
            {
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), m_Connection);
                //事物
                if (m_Transaction != null)
                {
                    cmd.Transaction = m_Transaction;
                }

                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);

                result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception exception)
            {
                error = exception.Message;
                LogHelps.LogHelp.AddLog<LogEncity>(exception);
                return result;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(DbSessionEntity dbSessionEntity)
        {
            try
            {

                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), m_Connection);
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);

                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dap.Fill(ds);

                DataTable dt = new DataTable();
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
                return dt;
            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="dbSessionEntity">实体</param>
        /// <param name="con">连接</param>
        /// <param name="error">错误信息</param>
        /// <returns></returns>
        public DataTable GetDataTable(DbSessionEntity dbSessionEntity, SqlConnection con, out string error)
        {
            DataTable dt = new DataTable();
            error = "";
            try
            {
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), con);
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);

                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dap.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                error = exception.Message;
                return dt; ;
            }
        }


        public DataTable ThreadGetDataTable(DbSessionEntity dbSessionEntity, SqlConnection con)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), con);
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);

                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dap.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                return dt; ;
            }
        }

        /// <summary>
        /// 从数据库中获取当前时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetDateTimeFromSQL()
        {
            SqlConnection conn =new SqlConnection(m_ConnectionString);
            try
            {
                string sql = "select getdate()";
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataReader reader = comm.ExecuteReader();
                DateTime dt;
                if (reader.Read())
                {
                    dt = (DateTime) reader[0];
                    return dt;
                }
            }
            catch (Exception exception)
            {
                LogHelps.LogHelp.AddLog<LogEncity>(exception);
            }
            finally
            {
                DisposeConnection(conn);
            }
            return DateTime.Now;

        }

        /// <summary>
        /// 线程安全的查询表格方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ThreadGetDataTable(DbSessionEntity dbSessionEntity, out string error)
        {
            SqlConnection con = new SqlConnection(m_ConnectionString);
            DataTable dt = new DataTable();
            error = "";
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), con);
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dap.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
                return dt;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return dt;
            }
            finally
            {
                DisposeConnection(con);
            }
        }

        public DataTable ThreadGetDataTable(DbSessionEntity dbSessionEntity)
        {
            SqlConnection con = new SqlConnection(m_ConnectionString);
            DataTable dt = new DataTable();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(dbSessionEntity.ToString(), con);
                //参数化查询 添加参数
                AddParameters(cmd, dbSessionEntity);
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dap.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
                return dt;
            }
            catch (Exception ex)
            {
                return dt;
            }
            finally
            {
                DisposeConnection(con);
            }
        }

        ///<summary>
               ///利用osql实现执行sql脚本文件
                /// </summary>
        //利用osql实现执行sql脚本文件
        public static void excutesqlfile(string user, string pwd, string databasename, string targetdir)
        {
            System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();
            sqlProcess.StartInfo.FileName = "osql.exe ";
            sqlProcess.StartInfo.Arguments = " -U " + user + " -P " + pwd + " -d " + databasename + " -i " + targetdir + "kmssql.sql";
            sqlProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
            sqlProcess.Start();
            sqlProcess.WaitForExit();//程序安装过程中执行
            sqlProcess.Close();
        }

        #endregion

        #region 事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            try
            {
                if (this.Connection != null)
                {
                    this.m_Transaction = this.Connection.BeginTransaction();
                }
            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="error"></param>

        public void Commit(out string error)
        {
            error = "";
            try
            {
                if (m_Transaction == null)
                {
                    error = "事务为空,检测是否开启事务";
                    return;
                }
                m_Transaction.Commit();
            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        /// <summary>
        /// 回滚
        /// </summary>
        /// <param name="error"></param>
        public void RollBack(out string error)
        {
            error = "";
            try
            {
                if (m_Transaction == null)
                {
                    error = "事务为空,检测是否开启事务";
                    return;
                }
                m_Transaction.Rollback();

            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        public void Commit()
        {
            string error = "";
            try
            {
                if (m_Transaction == null)
                {
                    error = "事务为空,检测是否开启事务";
                    throw new Exception(error);
                    return;
                }
                m_Transaction.Commit();
            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        /// <summary>
        /// 回滚
        /// </summary>
        /// <param name="error"></param>
        public void RollBack()
        {
            string error = "";
            try
            {
                if (m_Transaction == null)
                {
                    error = "事务为空,检测是否开启事务";
                    throw new Exception(error);
                    return;
                }
                m_Transaction.Rollback();

            }
            catch (Exception exception)
            {
                throw ExceptionFactory.CreateException(ExceptionType.DbsessionException, exception.Message);
            }
        }

        public void Dispose()
        {
            DisposeConnection(m_Connection);
        }


        #endregion

        #region 私有
        private static void DisposeConnection(SqlConnection sqlConnection)
        {
            if (sqlConnection != null)
            {
                sqlConnection.Dispose();
            }
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <param name="dbSessionEntity">数据实体类</param>
        private static void AddParameters(SqlCommand cmd, DbSessionEntity dbSessionEntity)
        {
            //参数化查询 添加参数
            if (dbSessionEntity.DictParameterInfos != null)
            {
                if (dbSessionEntity.DictParameterInfos.Count > 0)
                {
                    SqlParameter[] sqlParameters = new SqlParameter[dbSessionEntity.DictParameterInfos.Count];
                    int i = 0;
                    foreach (var key in dbSessionEntity.DictParameterInfos.Keys)
                    {
                        sqlParameters[i] = new SqlParameter(key, dbSessionEntity.DictParameterInfos[key].SqlValue);
                        i++;
                    }
                    cmd.Parameters.AddRange(sqlParameters);
                }
            }
        }

        #endregion
    }

}
